-- Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[p_WM_Hang_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_Hang_Insert]

IF OBJECT_ID(N'[dbo].[p_WM_Hang_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_Hang_Update]

IF OBJECT_ID(N'[dbo].[p_WM_Hang_InsertUpdate]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_Hang_InsertUpdate]

IF OBJECT_ID(N'[dbo].[p_WM_Hang_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_Hang_Delete]

IF OBJECT_ID(N'[dbo].[p_WM_Hang_DeleteDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_Hang_DeleteDynamic]

IF OBJECT_ID(N'[dbo].[p_WM_Hang_Load]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_Hang_Load]

IF OBJECT_ID(N'[dbo].[p_WM_Hang_SelectDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_Hang_SelectDynamic]

IF OBJECT_ID(N'[dbo].[p_WM_Hang_SelectAll]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_Hang_SelectAll]

IF OBJECT_ID(N'[dbo].[p_WM_Hang_SelectBy_DVT_ID]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_Hang_SelectBy_DVT_ID]

IF OBJECT_ID(N'[dbo].[p_WM_Hang_DeleteBy_DVT_ID]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_Hang_DeleteBy_DVT_ID]


GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_Hang_Insert]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_Hang_Insert]
	@Barcode varchar(20),
	@TenHang nvarchar(2000),
	@DVT_ID char(5),
	@GhiChu nvarchar(max)
AS
INSERT INTO [dbo].[t_WM_Hang]
(
	[Barcode],
	[TenHang],
	[DVT_ID],
	[GhiChu]
)
VALUES
(
	@Barcode,
	@TenHang,
	@DVT_ID,
	@GhiChu
)

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_Hang_Update]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_Hang_Update]
	@Barcode varchar(20),
	@TenHang nvarchar(2000),
	@DVT_ID char(5),
	@GhiChu nvarchar(max)
AS

UPDATE
	[dbo].[t_WM_Hang]
SET
	[TenHang] = @TenHang,
	[DVT_ID] = @DVT_ID,
	[GhiChu] = @GhiChu
WHERE
	[Barcode] = @Barcode

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_Hang_InsertUpdate]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_Hang_InsertUpdate]
	@Barcode varchar(20),
	@TenHang nvarchar(2000),
	@DVT_ID char(5),
	@GhiChu nvarchar(max)
AS
IF EXISTS(SELECT [Barcode] FROM [dbo].[t_WM_Hang] WHERE [Barcode] = @Barcode)
	BEGIN
		UPDATE
			[dbo].[t_WM_Hang] 
		SET
			[TenHang] = @TenHang,
			[DVT_ID] = @DVT_ID,
			[GhiChu] = @GhiChu
		WHERE
			[Barcode] = @Barcode
	END
ELSE
	BEGIN
	INSERT INTO [dbo].[t_WM_Hang]
	(
			[Barcode],
			[TenHang],
			[DVT_ID],
			[GhiChu]
	)
	VALUES
	(
			@Barcode,
			@TenHang,
			@DVT_ID,
			@GhiChu
	)	
	END
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_Hang_Delete]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_Hang_Delete]
	@Barcode varchar(20)
AS

DELETE FROM 
	[dbo].[t_WM_Hang]
WHERE
	[Barcode] = @Barcode

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_Hang_DeleteBy_DVT_ID]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_Hang_DeleteBy_DVT_ID]
	@DVT_ID char(5)
AS

DELETE FROM [dbo].[t_WM_Hang]
WHERE
	[DVT_ID] = @DVT_ID

GO

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_Hang_DeleteDynamic]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_Hang_DeleteDynamic]
	@WhereCondition NVARCHAR(500)
AS

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'DELETE FROM [dbo].[t_WM_Hang] WHERE ' + @WhereCondition

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_Hang_Load]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_Hang_Load]
	@Barcode varchar(20)
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[Barcode],
	[TenHang],
	[DVT_ID],
	[GhiChu]
FROM
	[dbo].[t_WM_Hang]
WHERE
	[Barcode] = @Barcode
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_Hang_SelectBy_DVT_ID]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_Hang_SelectBy_DVT_ID]
	@DVT_ID char(5)
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[Barcode],
	[TenHang],
	[DVT_ID],
	[GhiChu]
FROM
	[dbo].[t_WM_Hang]
WHERE
	[DVT_ID] = @DVT_ID

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_Hang_SelectDynamic]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_Hang_SelectDynamic]
	@WhereCondition NVARCHAR(500),
	@OrderByExpression NVARCHAR(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 
'SELECT 
	[Barcode],
	[TenHang],
	[DVT_ID],
	[GhiChu]
FROM [dbo].[t_WM_Hang] 
WHERE ' + @WhereCondition

IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression
END

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_Hang_SelectAll]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_Hang_SelectAll]
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[Barcode],
	[TenHang],
	[DVT_ID],
	[GhiChu]
FROM
	[dbo].[t_WM_Hang]	

GO

